﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;

/// <summary>
/// Summary description for ClassName
/// </summary>
public class RefDataManager
{
    
    
    public RefDataManager()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    
    public static dynamic CheckExistRefData(string prefType, string prefid, string refType, string refID, string refDesc, int active){

         string sql = "SELECT * FROM ReferenceData WHERE ParentRefType=@0 AND ParentRefID=@1 AND RefType=@2 AND RefID=@3 AND RefDesc=@4 and Active=@5";
        object[] param = new object[6];
            param[0]= prefType;
            param[1]= prefid;
            param[2]= refType;
            param[3]= refID;
            param[4]=refDesc;
            param[5]=active;
        var query = DBManager.Query(sql,param);
            if(query.Count>0){
                return query[0];
            }else{
                return null;    
            }
         

    }
    
      public static dynamic CheckExistChildRefData(string refType, string refDesc){

         string sql = "SELECT * FROM ReferenceData WHERE RefType=@0 AND RefDesc=@1";
        object[] param = new object[2];
            param[0]= refType;
            param[1]=refDesc;
        var query = DBManager.Query(sql,param);
            if(query.Count>0){
                return query[0];
            }else{
                return null;    
            }
         

    }
    
    public static int AddRefData(dynamic d){
        
        string sql =  string.Format(@"INSERT INTO ReferenceData(
            ParentRefType,
            ParentRefID,
            RefType,
            RefID,
            RefDesc,
            Active,
            CTCBCode,
            CTCBDesc,
            UBPHCode,
            UBPHDesc,
            PB3Code,
            PB3Desc,
            PB4Code,
            PB4Desc,
            PB5Code,
            PB5Desc,
            PB6Code,
            PB6Desc,
            PB7Code,
            PB7Desc,
            PB8Code,
            PB8Desc
            )
            VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}')",
d.ParentRefType,d.ParentRefID,d.RefType,d.RefID,d.RefDesc,d.Active,d.CTCBCode,d.CTCBDesc,d.UBPHCode,d.UBPHDesc,d.PB3Code,d.PB3Desc,d.PB4Code,d.PB4Desc,d.PB5Code,d.PB5Desc,d.PB6Code,d.PB6Desc,d.PB7Code,d.PB7Desc,d.PB8Code,d.PB8Desc);
        
        var result = DBManager.Execute(sql,null);

          return result;
          
    }
    
    public static int UpdateRefData(dynamic d){
        
        string sql =  string.Format(@"UPDATE ReferenceData
            SET ParentRefType='{0}',
            ParentRefID='{1}',
            RefType='{2}',
            RefID='{3}',
            RefDesc='{4}',
            Active='{5}',
            CTCBCode='{6}',
            CTCBDesc='{7}',
            UBPHCode='{8}',
            UBPHDesc='{9}',
            PB3Code='{10}',
            PB3Desc='{11}',
            PB4Code='{12}',
            PB4Desc='{13}',
            PB5Code='{14}',
            PB5Desc='{15}',
            PB6Code='{16}',
            PB6Desc='{17}',
            PB7Code='{18}',
            PB7Desc='{19}',
            PB8Code='{20}',
            PB8Desc='{21}'
            WHERE RefType='{22}' AND RefID='{23}' AND RefDesc='{24}'",
            d.ParentRefType,d.ParentRefID,d.RefType,d.RefID,d.RefDesc,d.Active,d.CTCBCode,d.CTCBDesc,d.UBPHCode,d.UBPHDesc,d.PB3Code,d.PB3Desc,d.PB4Code,d.PB4Desc,d.PB5Code,d.PB5Desc,d.PB6Code,d.PB6Desc,d.PB7Code,d.PB7Desc,d.PB8Code,d.PB8Desc,d.OldRefType,d.OldRefID,d.OldRefDesc);
        
        var result = DBManager.Execute(sql,null);
          return result;
          
    }
    
    
    
     public static int UpdateRefData2(dynamic d){
        
        string sql =  string.Format(@"UPDATE ReferenceData
            SET ParentRefType='{0}',
            ParentRefID='{1}',
            RefType='{2}',
            RefID='{3}',
            RefDesc='{4}',
            Active='{5}',
            CTCBCode='{6}',
            CTCBDesc='{7}',
            UBPHCode='{8}',
            UBPHDesc='{9}',
            PB3Code='{10}',
            PB3Desc='{11}',
            PB4Code='{12}',
            PB4Desc='{13}',
            PB5Code='{14}',
            PB5Desc='{15}',
            PB6Code='{16}',
            PB6Desc='{17}',
            PB7Code='{18}',
            PB7Desc='{19}',
            PB8Code='{20}',
            PB8Desc='{21}'
            WHERE RefType='{22}' AND RefID='{23}'",
            d.ParentRefType,d.ParentRefID,d.RefType,d.RefID,d.RefDesc,d.Active,d.CTCBCode,d.CTCBDesc,d.UBPHCode,d.UBPHDesc,d.PB3Code,d.PB3Desc,d.PB4Code,d.PB4Desc,d.PB5Code,d.PB5Desc,d.PB6Code,d.PB6Desc,d.PB7Code,d.PB7Desc,d.PB8Code,d.PB8Desc,d.OldRefType,d.OldRefID);
        
        var result = DBManager.Execute(sql,null);
          return result;
          
    }
    
     public static int AddChildData(string refType,string refId,string refDesc, int active){
        
        string sql =  @"insert into ReferenceData (RefType, RefID, RefDesc, Active)
                       VALUES(@0,@1,@2,@3);";
        object[] param = new object[4];
        param[0] = refType;
        param[1] = refId;
        param[2] = refDesc;
        param[3] = active;
        if (CheckExistChildRefData(refType,refDesc) == null){
            var result = DBManager.Execute(sql,param);
            return result;          
          }
        else{
            return 0;
        };
        
        
    }
    
  public static IEnumerable<dynamic> GetRefValues(string refType){
   
        string sql = "SELECT RefID, RefDesc from ReferenceData WHERE RefType=@0  AND Active='1' ORDER BY RefDesc ASC";
        var query = DBManager.Query(sql,new object[]{refType});
        return query;
    
  }

  public static IEnumerable<dynamic> GetRefValuesForMDT(string refType){
   
        string sql = "SELECT RefID, RefDesc from ReferenceData WHERE RefType=@0 AND Active='1' ORDER BY RefID ASC";
        var query = DBManager.Query(sql,new object[]{refType});
        return query;
    
  }
  public static IEnumerable<dynamic> GetChildRefValues(string parentRefID,string refType){
      string sql ="SELECT RefID,RefDesc FROM ReferenceData WHERE ParentRefID=@0 AND RefType=@1 AND Active='1'  ORDER BY RefDesc ASC";
      object[] param = new object[2];
      param[0] = parentRefID;
      param[1] = refType;
      var query = DBManager.Query(sql,param);
      return query;

    }
      public static dynamic GetRefDataValues(string refType,string refId,string refDesc){
   
        string sql = "select * from ReferenceData where RefType = @0 and RefId=@1 and RefDesc=@2";
      object[] param = new object[3];
           param[0]= refType;
                param[1]= refId;
               param[2]=refDesc;
      var query = DBManager.Query(sql,param);
        if(query.Count>0){
            return query[0];
        }else{
          return null;    
        }
        
    
  }
    
  public static dynamic GetRefDataValues2(string refType,string refId){
   
        string sql = "select * from ReferenceData where RefType = @0 and RefId=@1";
      object[] param = new object[2];
           param[0]= refType;
                param[1]= refId;
      var query = DBManager.Query(sql,param);
        if(query.Count>0){
            return query[0];
        }else{
          return null;    
        }
        
    
  }  
  
     public static IEnumerable<dynamic> GetParentRefId(string prefType){
           string sql;

            sql = "select ParentRefID from ReferenceData where ParentRefType = @0";

        var query = DBManager.Query(sql,new object[]{prefType});
        return query;
        
    
  }
    /// <summary>
	/// Get the countries per region
	/// </summary>
	/// <param name="parentRefType">parent region</param>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetCountryValuesByRegion(string parentRefID){
          return GetChildRefValues(parentRefID,"RT-00007");
    }
    
    public static IEnumerable<dynamic> GetCountryValues(){
        return GetRefValues("RT-00007");
    }

    public static dynamic GetCountryRegion(string country){
        string sql = @"SELECT ParentRefID FROM ReferenceData
                       WHERE  ParentRefType='RT-00003' 
                       AND RefType='RT-00007'
                       AND RefID=@0";

        var query = DBManager.Query(sql,new object[]{country});
        if(query.Count>0){
            return query[0];
        }else{
          return null;    
        }
       
    }
    
    /// <summary>
	/// Get the states per country
	/// </summary>
	/// <param name="parentRefID">selected country</param>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetStateValuesByCountry(string parentRefID){
         return GetChildRefValues(parentRefID,"RT-00012");
    }
    
     /// <summary>
	/// Get the values for the Region Reference data
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetRegionLkUpVal(){
        return GetRefValues("RT-00003");
    }
    
    /// <summary>
	/// Get Customer status lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetCustStatLkUpVal(){
        return GetRefValues("RT-00001");
    }
    
    /// <summary>
	/// Get customer class lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetCustClassLkUpVal(){
        return GetRefValues("RT-00002");
    }
    
    /// <summary>
	/// Get campaign lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetCampaignLkUpVal(){
        return GetRefValues("RT-00005");
    }
    
    /// <summary>
	/// Get solicitor lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetSolicitorLkUpVal(){
        return GetRefValues("RT-00004");
    }
    
    /// <summary>
	/// Get relationship to referror lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetRel2RefLkUpVal(){
        return GetRefValues("RT-00006");
    }
    
    /// <summary>
	/// Get gender lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetGenderLkUpVal(){
        return GetRefValues("RT-00008");
    }
    
    /// <summary>
	/// Get nationality lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetNatLkUpVal(){
        return GetRefValues("RT-00009");
    }
    
    /// <summary>
	/// Get civil status lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetCivilStatLkUpVal(){
        return GetRefValues("RT-00010");
    }
    
    /// <summary>
	/// Get ID type lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetIDTypeLkUpVal(){
        return GetRefValues("RT-00011");
    }
    
    /// <summary>
	/// Get source of funds lookup table
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetSrcOfFundsLkUpVal(){
        return GetRefValues("RT-00013");
    }
    
    /// <summary>
	/// Get employment type lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetEmpTypeLkUpVal(){
        return GetRefValues("RT-00014");
    }
    
    /// <summary>
	/// Get Designation/Rank lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetDesRankLkUpVal(){
        return GetRefValues("RT-00015");
    }
    
    /// <summary>
	/// Get nature of work lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetNatureOfWorkLkUpVal(){
        return GetRefValues("RT-00016");
    }
    
    /// <summary>
	/// Get mode of remit lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetModeOfRemitLkUpVal(){
        return GetRefValues("RT-00017");
    }
    
    /// <summary>
	/// Get call frequency lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetCallFreqLkUpVal(){
        return GetRefValues("RT-00018");
    }
    
    /// <summary>
	/// Get mode of call lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetModeOfCallLkUpVal(){
        return GetRefValues("RT-00019");
    }
    
    /// <summary>
	/// Get cargo frequency lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetCargoFreqLkUpVal(){
        return GetRefValues("RT-00020");
    }
    
    /// <summary>
	/// Get preferred mode of communication lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetPrefModeOfComLkUpVal(){
        return GetRefValues("RT-00021");
    }
    
    /// <summary>
	/// Get preferred day lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetPrefDayLkUpVal(){
        return GetRefValuesForMDT("RT-00022");
    }
    
    /// <summary>
	/// Get Remittance frequency lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetRemitFreqLkUpVal(){
        return GetRefValues("RT-00023");
    }
    
    /// <summary>
	/// Get preferred time lookup table
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetPrefTimeLkUpVal(){
        return GetRefValuesForMDT("RT-00024");
    }
    /// <summary>
	/// Get cash card status lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetCashCardStatLkUpVal(){
        return GetRefValues("RT-00025");
    }
    /// <summary>
	/// Get card delivery lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetCardDelLkUpVal(){
        return GetRefValues("RT-00026");
    }
    
    /// <summary>
	/// Get document status lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetDocStatLkUpVal(){
        return GetRefValues("RT-00027");
    }
    
    /// <summary>
	/// Get cash card type lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetCashCardTypeLkUpVal(){
        return GetRefValues("RT-00028");
    }
    
    /// <summary>
	/// Get month lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetMonthLkUpVal(){
        return GetRefValuesForMDT("RT-00029");
    }
    
    /// <summary>
	/// Get year lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetYearLkUpVal(){
        return GetRefValues("RT-00030");
    }
    
    /// <summary>
	/// Get partner bank lookup values
	/// </summary>
	/// <returns></returns>
    public static IEnumerable<dynamic> GetPartnerBankLkUpVal(){
        return GetRefValues("RT-00031");
    }
    
    /// <summary>
	/// Extract Region Reference values from excel
	/// </summary>
	/// <param name="path"></param>
	/// <param name="sh"></param>
	/// <returns></returns>
     public static List<Dictionary<string,string>> ExtRefDataFromXLS(string path,string sh){
        try{
            ExcelManager.Template=path;
            HSSFWorkbook wb = ExcelManager.InitializeWB("MyRemit Cash Card");
            ISheet sheet = wb.GetSheet(sh);
            int dRow=2;
            int hRow=0;
            //check Ref Type
            string[] colH = {"ParentRefType",
                "ParentRefID",
                "RefType",
                "RefID",
                "Active",
                "RefDesc",
              };
            
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            List<Dictionary<string,string>> regionList = new List<Dictionary<string,string>>();
            while (rows.MoveNext()){
                Dictionary<string,string> cc = new Dictionary<string,string>();
                IRow r=(HSSFRow)rows.Current;
                for(int i=0;i<colH.Length;i++){
                    ICell cell = r.GetCell(i);
                    if(cell!=null){
                        cc[colH[i]]=cell.ToString();
                   }else{
                       cc[colH[i]]="";
                    }
                }    
                 regionList.Add(cc);
            }
            return regionList;
            
        }catch(Exception e){
            throw e;
        }
    }
    
    
    
    public static dynamic GetMapID(string refType,string id){
        try{
            string sql =string.Format("SELECT RefID FROM ReferenceData WHERE RefType='{0}' AND MapID='{1}'",refType,id);
            var query = DBManager.Query(sql,null);
            return query[0].RefID;
        }catch(Exception e){
            throw e;
        }
    }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
   
    
}
